import json
import pandas as pd
from common.database import OLAP
from common.log import log_handler

log = log_handler.LogHandler().get_log()


def run():
    path = "test.txt"
    f = open(path, "r", encoding="utf-8")
    kg_detail = json.load(f)
    vertices_df = pd.DataFrame(kg_detail["vertices"])[["id", "label", "class", "attrs"]]
    srid = 4326

    ids = []
    lons = []
    lats = []
    for i in range(len(vertices_df)):
        attrs = vertices_df.loc[i, "attrs"]
        if '经度' in attrs and '纬度' in attrs:
            lon = attrs['经度']
            lat = attrs['纬度']
            lons.append(lon)
            lats.append(lat)
            ids.append(i)
    filtered_df = vertices_df.loc[ids]
    filtered_df["lon"] = lons
    filtered_df["lat"] = lats

    edges_df = pd.DataFrame(kg_detail["edges"])
    source_df = pd.merge(filtered_df, edges_df, left_on="id", right_on="source")[["id_y", "lon", "lat"]]
    target_df = pd.merge(filtered_df, edges_df, left_on="id", right_on="target")[
        ["id_y", "label_y", "attrs_y", "lon", "lat"]]
    merge_df = pd.merge(source_df, target_df, on="id_y")
    merge_df.columns = ["id", "lon_x", "lat_x", "label", "attrs", "lon_y", "lat_y"]
    OLAP.save_dataframe("dataset.test_kg", merge_df)
    ret_df = OLAP.execute_query(
        f"select *, st_makeline(ST_SetSRID(st_point(lon_x,lat_x),{srid}), ST_SetSRID(st_point(lon_y,lat_y),{srid})) as geom from dataset.test_kg")
    log.info(ret_df)
